package com.summer.dao;

import com.summer.bean.Student;
import com.summer.utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Created by IntelliJ IDEA.
 * Description:
 * User: 35391
 * Package: com.summer.dao
 * Date: 2025/7/22
 * Time: 下午10:14
 * Version: 1.0
 */
public class StudentDao {
    public static Student selectByUsername(String username){
        Student student = new Student();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "select * from student where sno = (select sno from user where username = ?);";
            ps = conn.prepareStatement(sql);
            ps.setString(1, username);
            rs = ps.executeQuery();
            if(rs.next()) {
                student.setSno(rs.getString("sno"));
                student.setSname(rs.getString("sname"));
                student.setSdept(rs.getString("sdept"));
                student.setAddress(rs.getString("address"));
                student.setGender(rs.getString("gender"));
                student.setDOB(rs.getDate("DOB"));
                student.setTelephone(rs.getString("telephone"));
                student.setDegree(rs.getString("degree"));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.close(conn, ps, rs);
        }
        return student;
    }

    public static int insert(Student student){
        int result = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "insert into student values(?,?,?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, student.getSno());
            ps.setString(2, student.getSname());
            ps.setString(3, student.getSdept());
            ps.setString(4, student.getAddress());
            ps.setString(5, student.getGender());
            ps.setDate(6, new java.sql.Date(student.getDOB().getTime()));
            ps.setString(7, student.getTelephone());
            ps.setString(8, student.getDegree());
            result = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.close(conn, ps, rs);
        }
        return result;
    }

    public static Student selectBySno(String sno){
        Student student = new Student();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "select * from student where sno = ?;";
            ps = conn.prepareStatement(sql);
            ps.setString(1, sno);
            rs = ps.executeQuery();
            if(rs.next()) {
                student.setSno(rs.getString("sno"));
                student.setSname(rs.getString("sname"));
                student.setSdept(rs.getString("sdept"));
                student.setAddress(rs.getString("address"));
                student.setGender(rs.getString("gender"));
                student.setDOB(rs.getDate("DOB"));
                student.setTelephone(rs.getString("telephone"));
                student.setDegree(rs.getString("degree"));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.close(conn, ps, rs);
        }
        return student;
    }

    public static int upDateBySno(Student student){
        int result = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "update student set sname = ?,sdept = ?,address = ?,gender = ?,DOB = ?,telephone = ?,degree = ? where sno = ?;";
            ps = conn.prepareStatement(sql);
            ps.setString(1, student.getSname());
            ps.setString(2, student.getSdept());
            ps.setString(3, student.getAddress());
            ps.setString(4, student.getGender());
            ps.setDate(5, new java.sql.Date(student.getDOB().getTime()));
            ps.setString(6, student.getTelephone());
            ps.setString(7, student.getDegree());
            ps.setString(8, student.getSno());
            result = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.close(conn, ps, rs);
        }
        return result;
    }
}
